﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;


namespace InputDocument.Models
{
    class StudentServiceForXls
    {
        /// <summary>
        /// 读取xls文件
        /// </summary>
        /// <returns></returns>
        public static List<Student> ReadFromXls()
        {
            var students = new List<Student>();//和 Student stu=new Student();区别？？？
            string filePath = @"学生.xls";
            //根据已有xls创建workbook对象
            HSSFWorkbook wb;
            FileStream file;
            file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            wb = new HSSFWorkbook(file);
            file.Close();
            //在workbook基础上打开sheet
            HSSFSheet sheet;
            sheet = (HSSFSheet)wb.GetSheet("sheet1");
            //在sheet中读取每一行的数据并放入到类中
            for (int i = 1; i < sheet.LastRowNum; i++)//确定有多少行数据，第一行数据从0开始
            {
                IRow row = sheet.GetRow(i);
                students.Add(new Student(
                    row.GetCell(0).ToString(),
                    row.GetCell(1).ToString(),
                    row.GetCell(2).ToString(),
                    Convert.ToInt32(row.GetCell(3).ToString()),
                    Convert.ToDateTime(row.GetCell(4).ToString())
                ));
            }
            return students;

        }
        /// <summary>
        /// 输出数据中的平均值，成绩最高及最低的学生的学生信息
        /// </summary>
        /// <param name="students"></param>
        public static void OutputScore(List<Student> students)
        {
            if (!students.Any())//判断数据是否为空？
            {
                Console.WriteLine("there is no record!");
                return;
            }
            var average = students.Average(a => a.Score);//求成绩的平均值？
            Console.WriteLine($"xls中成绩的平均值为{average}");
            var max = students.Max(a => a.Score);
            var min = students.Min(a => a.Score);
            foreach (var item in students)//求出并输出最大最小成绩学生信息
            {
                if (item.Score == max)
                    Console.WriteLine($"成绩最高分:{max}分，对应学生姓名:{item.Name},学号:{item.StudentNumber},电话:{item.PhoneNumber},生日:{item.Birthday.ToLongDateString()},年龄:{item.Age}");//这里的birthday转换了？
                if (item.Score == min)
                    Console.WriteLine($"成绩最低分:{min}分，对应学生姓名:{item.Name},学号:{item.StudentNumber},电话:{item.PhoneNumber},生日:{item.Birthday.ToLongDateString()},年龄:{item.Age}");//这里的birthday转换了？
            }
        }
    }

}
